﻿<cfsilent>
	<cfscript>
		
		termID = event.getArg("TermID");
		depID = event.getArg("DepID");
		
		sql = "	SELECT
   					 i.institute_id,i.institute_name
				FROM
					t_institute i
				WHERE
					i.institute_id = :depID ";
		
		queryObj = new Query( datasource=application.dnsSlave );
		
		queryObj.addParam( name="depID", value=depID, cfsqltype="cf_sql_varchar" );

        rs_departmentEntity= queryObj.execute( sql=sql ).getResult();
		
		sql = "	SELECT
					clz.cls_name,clz.cls_size,sbj.sbj_short,clz.grade,
					p.sbj_direction,
					c.course_name,cn.cna_name,cp.prop_name,c.course_credit,i.institute_short,
					c.period_theory,c.period_practice,c.period_computer,
					c.course_code
				FROM
					t_plan p
					INNER JOIN t_class clz ON clz.cls_id = p.cls_id
					INNER JOIN t_subject sbj ON sbj.sbj_id = clz.sbj_id
					INNER JOIN t_course c ON c.cid = p.cid
					INNER JOIN t_course_nature cn ON cn.cna_id = p.cna_id
					INNER JOIN t_course_prop cp ON cp.prop_id = p.prop_id
					INNER JOIN t_institute i ON i.institute_id = c.institute_id
				WHERE
						p.term_id = :term 
					AND
						sbj.institute_id = :dep 
				ORDER BY
					p.sbj_direction,cp.prop_name,c.course_name";

		queryObj = new Query( datasource=application.dnsSlave );
		
		queryObj.addParam( name="term", value=termID, cfsqltype="cf_sql_char" );
		queryObj.addParam( name="dep", value=depID, cfsqltype="cf_sql_varchar" );

        rs_plan = queryObj.execute( sql=sql ).getResult();

		temFile = GetTempDirectory() & createUUID() & ".xls";
		downFile = URLEncodedFormat(rs_departmentEntity.institute_name & "学期教学计划", "utf-8");
		
		excel = getProperty("serviceFactory").getBean("spreadSheetObject");
		
		/* 创建工作簿 */
		spreadsheetObj = excel.SpreadSheetNew("班级教学计划", false);
		
		/* 添加表头 */
		excel.SpreadsheetAddrow(spreadsheetObj, "班级,计划人数,专业简称,年级,修读方向,课程,课程类别,课程性质,学分,任课单位,理论学时,实践学时,上机学时,课程号");
		
		row = 1;
		
		for(a = 1; a lte rs_plan.recordCount; a++){
			
			row++;
			
			/* 构建计划信息 */
			rowData = rs_plan['cls_name'][a]
						& "," & rs_plan['cls_size'][a]
						& "," & rs_plan['sbj_short'][a]
						& "," & rs_plan['grade'][a]
						& "," & rs_plan['sbj_direction'][a]
						& "," & rs_plan['course_name'][a]
						& "," & rs_plan['cna_name'][a]
						& "," & rs_plan['prop_name'][a]
						& "," & numberFormat( rs_plan['course_credit'][a], '_.__' )
						& "," & rs_plan['institute_short'][a]
						& "," & rs_plan['period_theory'][a]
						& "," & rs_plan['period_practice'][a]
						& "," & rs_plan['period_computer'][a]
						& "," & rs_plan['course_code'][a];
			
			/* 写入计划信息 */
			excel.SpreadsheetAddrow(spreadsheetObj, rowData);
			
		}
		
		excel.SpreadSheetWrite(spreadsheetObj, temFile, true);
	
	</cfscript>

	<cfheader name="Content-Disposition" value="attachment; filename=#downFile#.xls" />
	<cfcontent file="#temFile#" reset="yes" type="application/msexcel" deletefile="yes" />

</cfsilent>